import json
import os
import sqlite3
from datetime import datetime

from fastapi import HTTPException
from sqlalchemy.ext.asyncio import AsyncSession

from config.constant import CommonConstant
from config.env import KylinConfig
from exceptions.exception import ServiceException
from module_admin.entity.vo.common_vo import CrudResponseModel
from module_kylin.dao.future_strategy_instance_dao import FutureStrategyInstanceDao
from module_kylin.dao.future_strategy_param_dao import FutureStrategyParamDao
from module_kylin.dao.future_strategy_position_dao import FutureStrategyPositionDao
from module_kylin.dao.strategy_tradebook_dao import StrategyTradebookDao
from module_kylin.entity.vo.future_strategy_instance_vo import (
    FutureStrategyInstanceModel,
    FutureStrategyInstancePageQueryModel,
    DeleteFutureStrategyInstanceModel,
)
from module_kylin.entity.vo.future_strategy_param_vo import FutureStrategyParamModel
from module_kylin.entity.vo.future_strategy_position_vo import FutureStrategyPositionModel
from module_kylin.entity.vo.strategy_tradebook_vo import StrategyTradebookModel
from utils.common_util import CamelCaseUtil
from utils.log_util import logger


class FutureStrategyInstanceService:
    """
    期货策略实例服务层
    """

    @classmethod
    async def get_strategy_instance_list_services(cls, db: AsyncSession,
                                                  query_model: FutureStrategyInstancePageQueryModel):
        """
        获取策略实例列表service

        :param db: 数据库会话
        :param query_model: 查询参数
        :return: 策略实例列表
        """
        try:
            # 获取策略实例列表
            strategy_instance_page = await FutureStrategyInstanceDao.get_strategy_instance_list(db, query_model)

            return strategy_instance_page
        except Exception as e:
            raise HTTPException(status_code=500, detail=str(e))

    @classmethod
    async def get_strategy_instance_detail_services(cls, db: AsyncSession, instance_id: int):
        """
        获取策略实例详情service

        :param db: 数据库会话
        :param instance_id: 策略实例id
        :return: 策略实例详情
        """
        try:
            # 获取策略实例详情
            strategy_instance_detail = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_id(db,
                                                                                                          instance_id)

            if not strategy_instance_detail:
                raise HTTPException(status_code=404, detail=f"策略实例{instance_id}不存在")
            # 转换为模型并返回
            result = FutureStrategyInstanceModel(**CamelCaseUtil.transform_result(strategy_instance_detail))
            return result
        except HTTPException as e:
            raise e
        except Exception as e:
            raise HTTPException(status_code=500, detail=str(e))

    @classmethod
    async def check_strategy_instance_unique_services(cls, db: AsyncSession, instance: FutureStrategyInstanceModel):
        """
        校验策略实例是否唯一service

        :param db: 数据库会话
        :param instance: 策略实例对象
        :return: 校验结果
        """
        id = -1 if instance.id is None else instance.id
        existing_instance = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_info(
            db, FutureStrategyInstanceModel(
                server=instance.server,
                group=instance.group,
                instance=instance.instance
            )
        )
        if existing_instance:
            if existing_instance and existing_instance.id != id:
                return CommonConstant.NOT_UNIQUE
        return CommonConstant.UNIQUE

    @classmethod
    async def add_strategy_instance_services(cls, db: AsyncSession, instance: FutureStrategyInstanceModel):
        """
        添加策略实例service

        :param db: 数据库会话
        :param instance: 策略实例信息
        :return: 操作结果
        """
        try:
            # 检查策略实例是否已存在
            if not await cls.check_strategy_instance_unique_services(db, instance):
                raise ServiceException(
                    message=f'新增策略实例{instance.instance}失败，该策略实例已存在'
                )

            # 添加策略实例
            await FutureStrategyInstanceDao.add_strategy_instance(db, instance)
            await db.commit()

            return CrudResponseModel(is_success=True, message='新增策略实例成功')
        except ServiceException as e:
            await db.rollback()
            raise e
        except Exception as e:
            await db.rollback()
            raise HTTPException(status_code=500, detail=str(e))

    @classmethod
    async def _import_tradebook_info(cls, db: AsyncSession, server: str, group: str,
                                    instance_name: str, db_file_path: str):
        """
        从SQLite数据库文件中导入策略实例的交易登记簿信息
        
        :param db: 数据库会话
        :param server: 服务器名称
        :param group: 策略组名称
        :param instance_name: 策略实例名称
        :param db_file_path: SQLite数据库文件路径
        """
        import sqlite3
        
        # 连接到SQLite数据库
        conn = None
        try:           
            # 连接SQLite数据库
            conn = sqlite3.connect(db_file_path)
            cursor = conn.cursor()
            
            # 检查表是否存在
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='tradebook'")
            table_exists = cursor.fetchone() is not None
            
            if not table_exists:
                logger.warning(f"交易登记簿表在文件{{db_file_path}}中不存在")
                return
            
            # 查询交易登记簿表中的所有字段
            cursor.execute("PRAGMA table_info(tradebook)")
            columns = [column[1] for column in cursor.fetchall()]
            
            # 构建查询语句，只查询我们需要的字段
            required_columns = ['price', 'buy', 'sell', 'bought', 'sold']
            available_columns = [col for col in required_columns if col in columns]
            
            if not available_columns:
                logger.warning(f"交易登记簿表中没有我们需要的字段")
                return
            
            # 查询交易登记簿数据 - 导入全部记录
            cursor.execute(f"SELECT {', '.join(available_columns)} FROM tradebook")
            all_tradebook_data = cursor.fetchall()
            
            if all_tradebook_data:
                # 遍历所有记录并处理
                for tradebook_data in all_tradebook_data:
                    # 构建StrategyTradebookModel对象
                    tradebook_dict = {
                        'name': instance_name,
                        'price': 0,
                        'buy': 0,
                        'sell': 0,
                        'bought': 0,
                        'sold': 0
                    }
                    
                    # 根据实际查询到的字段填充数据
                    for i, col in enumerate(available_columns):
                        tradebook_dict[col] = tradebook_data[i]
                    
                    tradebook_model = StrategyTradebookModel(**tradebook_dict)
                    
                    # 检查是否已存在该策略实例的交易登记簿记录
                    existing_tradebook = await StrategyTradebookDao.get_strategy_tradebook_by_info(db, tradebook_model)
                    
                    if existing_tradebook:
                        # 更新现有记录
                        await StrategyTradebookDao.update_strategy_tradebook(db, existing_tradebook.id, tradebook_model)
                    else:
                        # 新增记录
                        await StrategyTradebookDao.add_strategy_tradebook(db, tradebook_model)
        except sqlite3.Error as e:
            logger.error(f"从SQLite数据库导入交易登记簿信息时出错: {{e}}", e=e)
            raise Exception(f"从SQLite数据库导入交易登记簿信息失败: {{str(e)}}")
        finally:
            # 关闭数据库连接
            if conn:
                conn.close()
    
    @classmethod
    async def _import_position_info(cls, db: AsyncSession, server: str, group: str,
                                    instance_name: str, db_file_path: str):
        """
        从SQLite数据库文件中导入策略实例的仓位信息
        
        :param db: 数据库会话
        :param server: 服务器
        :param group: 组名
        :param instance_name: 策略实例名称
        :param db_file_path: SQLite数据库文件路径
        """
        try:
            # 使用同步方式读取SQLite文件
            # 在实际生产环境中，可能需要考虑使用异步SQLite库
            conn = sqlite3.connect(db_file_path)
            cursor = conn.cursor()

            # 查询position表中的数据 - 使用当前系统日期代替数据库中的date列
            # 首先检查表结构，找出实际的列名
            cursor.execute("PRAGMA table_info(position)")
            columns = [column[1] for column in cursor.fetchall()]

            # 根据实际列名构建查询语句
            # 我们需要确保查询包含position, long_pos/long_position, short_pos/short_position等字段
            # 默认使用当前系统日期作为date值
            current_date = datetime.now().strftime('%Y-%m-%d')

            # 初始化positions变量，避免后续使用时出现NameError
            positions = []

            # 根据表结构动态调整查询字段 - 基于实际表结构: name, pos, pos_long, pos_short等
            if 'pos' in columns and 'pos_long' in columns and 'pos_short' in columns:
                # 实际表结构中有pos, pos_long, pos_short等字段
                # noinspection SqlResolve
                cursor.execute("SELECT name, pos, pos_long, pos_short FROM position")
                positions = cursor.fetchall()

                # 处理查询结果
                for pos in positions:
                    name_value, position_value, long_position_value, short_position_value = pos

                    # 根据实际表结构和业务需求构建策略实例名称
                    # 结合instance_name和name字段的值，或者根据业务需求选择其中一个
                    # 这里使用instance_name作为策略实例名称，name字段可以作为额外信息记录或忽略
                    full_instance_name = instance_name

                    # 构建仓位信息对象 - 使用当前系统日期
                    # noinspection PyArgumentList
                    position_model = FutureStrategyPositionModel(
                        date=current_date,
                        strategyInstance=full_instance_name,
                        position=float(position_value) if position_value else 0.0,
                        longPosition=float(long_position_value) if long_position_value else 0.0,
                        shortPosition=float(short_position_value) if short_position_value else 0.0
                    )

                    logger.debug(f'处理仓位信息 - 策略实例: {full_instance_name}, 名称: {name_value}, '
                                 f'总仓位: {position_value}, 多仓: {long_position_value}, 空仓: {short_position_value}')

                    # 检查仓位信息是否已存在
                    existing_position = await FutureStrategyPositionDao.get_strategy_position_by_info(
                        db, position_model
                    )

                    if existing_position:
                        # 如果已存在，则更新
                        await FutureStrategyPositionDao.update_strategy_position(
                            db, existing_position.id, position_model
                        )
                    else:
                        # 如果不存在，则新增
                        await FutureStrategyPositionDao.add_strategy_position(db, position_model)
            else:
                # 表结构不满足条件，记录日志
                logger.warning(f'文件{db_file_path}中的position表结构不符合预期，缺少必要的列')

            logger.info(f'成功导入实例{instance_name}的{len(positions)}条仓位信息')
        except sqlite3.OperationalError as oe:
            # 处理SQLite操作错误，特别是表不存在的情况
            if "no such table: position" in str(oe):
                logger.warning(f'文件{db_file_path}中不存在position表，跳过仓位信息导入')
            else:
                raise
        except Exception as e:
            logger.error(f'处理SQLite文件{db_file_path}时出错：{str(e)}')
            raise
        finally:
            # 确保关闭数据库连接
            if 'conn' in locals():
                conn.close()

    @classmethod
    async def update_strategy_instance_services(cls, db: AsyncSession, instance_id: int,
                                                instance: FutureStrategyInstanceModel):
        """
        更新策略实例service

        :param db: 数据库会话
        :param instance_id: 策略实例ID
        :param instance: 策略实例信息
        :return: 操作结果
        """
        try:
            # 检查策略实例是否存在
            existing_instance = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_id(db, instance_id)
            if not existing_instance:
                raise ServiceException(message=f'策略实例{instance_id}不存在')

            # 设置更新的ID
            instance.id = existing_instance.id
            # 检查唯一性
            if not await cls.check_strategy_instance_unique_services(db, instance):
                raise ServiceException(
                    message=f'更新策略实例{existing_instance.instance}失败，该策略实例已存在'
                )

            # 更新策略实例（使用现有实例名）
            await FutureStrategyInstanceDao.update_strategy_instance(db, existing_instance.instance, instance)
            await db.commit()

            return CrudResponseModel(is_success=True, message='更新策略实例成功')
        except ServiceException as e:
            await db.rollback()
            raise e
        except Exception as e:
            await db.rollback()
            raise HTTPException(status_code=500, detail=str(e))

    @classmethod
    async def delete_strategy_instance_services(cls, db: AsyncSession, delete_model: DeleteFutureStrategyInstanceModel):
        """
        删除策略实例service

        :param db: 数据库会话
        :param delete_model: 删除参数
        :return: 操作结果
        """
        try:
            # 确定使用的是实例名列表还是ID列表
            if delete_model.instance_names:
                instance_names = delete_model.instance_names
                # 检查策略实例是否存在
                for instance_name in instance_names:
                    existing_instance = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_instance(db,
                                                                                                                 instance_name)
                    if not existing_instance:
                        raise ServiceException(message=f'策略实例{instance_name}不存在')
            elif delete_model.ids:
                # 根据ID查询实例名
                instance_names = []
                for instance_id in delete_model.ids:
                    existing_instance = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_id(db,
                                                                                                           instance_id)
                    if not existing_instance:
                        raise ServiceException(message=f'策略实例ID {instance_id} 不存在')
                    instance_names.append(existing_instance.instance)
            else:
                raise ServiceException(message='传入策略实例名或ID列表不能为空')

            # 批量删除策略实例
            await FutureStrategyInstanceDao.batch_delete_strategy_instance(db, instance_names)
            await db.commit()

            return CrudResponseModel(is_success=True, message='删除策略实例成功')
        except ServiceException as e:
            await db.rollback()
            raise e
        except Exception as e:
            await db.rollback()
            raise HTTPException(status_code=500, detail=str(e))

    @classmethod
    async def import_strategy_instance_services(cls, db: AsyncSession):
        """
        自动从特定目录下查找cta_strategy_setting.json文件并导入策略实例service

        :param db: 数据库会话
        :return: 操作结果
        """
        try:
            # 获取文件根路径（根据当前环境自动选择配置）
            root_path = KylinConfig['KYLIN_FILE_PATH']

            # 检查根路径是否存在
            if not os.path.exists(root_path):
                raise ServiceException(message=f'根路径不存在：{root_path}')

            total_imported_count = 0
            total_files_processed = 0

            # 清空策略参数表
            await FutureStrategyParamDao.truncate_strategy_param(db)
            logger.info('成功清空策略参数表')
            # 清空交易登记簿表
            await StrategyTradebookDao.truncate_strategy_tradebook(db)
            logger.info('成功清空交易登记簿表')

            # 遍历根目录下的所有服务器目录
            for server in os.listdir(root_path):
                server_path = os.path.join(root_path, server)
                if not os.path.isdir(server_path):
                    continue

                # 遍历服务器目录下的所有组名目录
                for group in os.listdir(server_path):
                    group_path = os.path.join(server_path, group)
                    if not os.path.isdir(group_path):
                        continue

                    # 构建文件路径：root_path/server/group/cta_strategy_setting.json
                    file_path = os.path.join(group_path, 'cta_strategy_setting.json')

                    # 检查文件是否存在
                    if not os.path.exists(file_path):
                        logger.warning(f'文件不存在：{file_path}，跳过该目录')
                        continue

                    total_files_processed += 1
                    imported_count = 0

                    try:
                        # 读取并解析JSON文件
                        logger.debug(f'尝试读取文件：{file_path}')
                        with open(file_path, 'r', encoding='utf-8-sig') as f:  # 使用utf-8-sig处理BOM
                            file_content = f.read()
                            logger.debug(f'文件内容：{file_content[:200]}...')  # 记录前200个字符
                            config_data = json.loads(file_content)

                        # 检查数据格式是否正确
                        logger.debug(f'解析后的数据类型：{type(config_data)}')
                        if not isinstance(config_data, dict):
                            logger.warning(f'文件{file_path}格式错误，应为字典格式，跳过该文件')
                            continue

                        logger.info(f'成功解析文件{file_path}，包含{len(config_data)}个策略实例')

                        # 遍历配置数据，导入策略实例
                        for instance_name, instance_info in config_data.items():
                            try:
                                logger.debug(f'处理实例：{instance_name}，实例信息：{instance_info}')

                                # 检查必要字段是否存在
                                class_name = instance_info.get('class_name')
                                vt_symbol = instance_info.get('vt_symbol')

                                logger.debug(f'提取的字段值 - class_name: {class_name}, vt_symbol: {vt_symbol}')

                                # 跳过缺少必要字段的实例
                                if not class_name or not vt_symbol or class_name.strip() == '' or vt_symbol.strip() == '':
                                    logger.warning(
                                        f'处理文件{file_path}中的实例{instance_name}时发现缺少必要字段（class_name={class_name}, vt_symbol={vt_symbol}），跳过该实例')
                                    continue

                                # 构建策略实例对象
                                # noinspection PyArgumentList
                                strategy_instance = FutureStrategyInstanceModel(
                                    server=server,
                                    group=group,
                                    instance=instance_name,
                                    className=class_name,
                                    vtSymbol=vt_symbol
                                )

                                # 检查策略实例是否已存在
                                existing_instance = await FutureStrategyInstanceDao.get_strategy_instance_detail_by_info(
                                    db, FutureStrategyInstanceModel(
                                        server=server,
                                        group=group,
                                        instance=instance_name
                                    )
                                )

                                if existing_instance:
                                    # 如果已存在，则更新
                                    await FutureStrategyInstanceDao.update_strategy_instance(db,
                                                                                             existing_instance.instance,
                                                                                             strategy_instance)
                                else:
                                    # 如果不存在，则新增
                                    await FutureStrategyInstanceDao.add_strategy_instance(db, strategy_instance)
                                    imported_count += 1
                                    total_imported_count += 1

                                # 导入策略参数（从setting字段中获取）
                                setting = instance_info.get('setting')
                                if setting and isinstance(setting, dict):
                                    try:
                                        logger.debug(f'导入实例{instance_name}的策略参数')
                                        # 遍历setting中的所有参数
                                        for param_name, param_value in setting.items():
                                            try:
                                                # 创建策略参数对象
                                                # noinspection PyArgumentList
                                                strategy_param = FutureStrategyParamModel(
                                                    strategyInstance=instance_name,
                                                    paramName=param_name,
                                                    paramValue=str(param_value)
                                                )

                                                # 检查参数是否已存在
                                                existing_param = await FutureStrategyParamDao.get_strategy_param_by_info(
                                                    db, strategy_param)
                                                if existing_param:
                                                    # 如果已存在，则更新
                                                    await FutureStrategyParamDao.update_strategy_param(db,
                                                                                                       existing_param.id,
                                                                                                       strategy_param)
                                                else:
                                                    # 如果不存在，则新增
                                                    await FutureStrategyParamDao.add_strategy_param(db, strategy_param)
                                            except Exception as param_error:
                                                logger.warning(f'导入参数{param_name}时出错：{str(param_error)}')
                                                continue
                                    except Exception as setting_error:
                                        logger.warning(f'导入实例{instance_name}的策略参数时出错：{str(setting_error)}')

                                # 尝试导入该策略实例的仓位信息
                                try:
                                    # 构建策略实例数据文件路径
                                    db_file_path = os.path.join(group_path, f'{instance_name}.db')
                                    if os.path.exists(db_file_path):
                                        await cls._import_position_info(db, server, group, instance_name, db_file_path)
                                        # 尝试导入策略实例交易登记簿信息
                                        try:
                                            await cls._import_tradebook_info(db, server, group, instance_name, db_file_path)
                                            logger.info(f'成功导入实例{instance_name}的交易登记簿信息')
                                        except Exception as tradebook_error:
                                            logger.warning(
                                                f'导入实例{instance_name}的交易登记簿信息时出错：{str(tradebook_error)}')
                                except Exception as position_error:
                                    logger.warning(
                                        f'导入实例{instance_name}的仓位信息时出错：{str(position_error)}')

                            except Exception as instance_error:
                                logger.warning(
                                    f'处理文件{file_path}中的实例{instance_name}时出错：{str(instance_error)}')
                                continue

                        logger.info(f'成功处理文件{file_path}，新增{imported_count}个策略实例')
                    except Exception as file_error:
                        logger.error(f'处理文件{file_path}时出错：{str(file_error)}')
                        continue

            await db.commit()

            if total_files_processed == 0:
                return CrudResponseModel(is_success=True, message='没有找到任何cta_strategy_setting.json文件需要导入')

            return CrudResponseModel(is_success=True,
                                     message=f'策略实例导入完成，共处理{total_files_processed}个文件，新增{total_imported_count}个策略实例')
        except ServiceException as e:
            await db.rollback()
            raise e
        except Exception as e:
            await db.rollback()
            raise HTTPException(status_code=500, detail=str(e))
